package com.epam.iunna.bookshop.model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;

import com.epam.iunna.bookshop.model.util.ConnectionsPool;
import com.epam.iunna.bookshop.model.util.DBUtils;
import com.epam.iunna.bookshop.model.util.HashGenerator;

/**
 * @author Iunna
 * 
 */
public class UserDao {

	public User getById(Long id) throws SQLException {
		Connection connection = null;
		ResultSet result = null;
		PreparedStatement statement = null;
		User user = null;

		try {

			connection = ConnectionsPool.getInstance().getConnection();

			// Do we have a connection?
			if (!connection.isClosed()) {
				// We have a MySQL Connection.
				statement = connection
						.prepareStatement("select * from user where id=?");
				statement.setLong(1, id);
				statement.executeQuery();
				result = statement.getResultSet();
				if (result.next()) {
					return getUserPojo(result);
				}
			}
		} finally {
		    DBUtils.closeConnection(connection, statement, result);
		}
		return user;
	}

	public User getByLogin(String login) throws SQLException {
		Connection connection = null;
		ResultSet result = null;
		PreparedStatement statement = null;
		User user = null;

		try {

			connection = ConnectionsPool.getInstance().getConnection();

			// Do we have a connection?
			if (!connection.isClosed()) {
				// We have a MySQL Connection.
				statement = connection
						.prepareStatement("select * from user where login=?");
				statement.setString(1, login);
				statement.executeQuery();
				result = statement.getResultSet();
				if (result.next()) {
					return getUserPojo(result);
				}
			}
		} finally {
		    DBUtils.closeConnection(connection, statement, result);
		}
		return user;
	}

	public User getByLoginPass(String login, String pass) throws SQLException {
        Connection connection = null;
        ResultSet result = null;
        PreparedStatement statement = null;
        User user = null;

        try {

            connection = ConnectionsPool.getInstance().getConnection();

            // Do we have a connection?
            if (!connection.isClosed()) {
                // We have a MySQL Connection.
                statement = connection
                        .prepareStatement("select * from user where login=? and pass=?");
                statement.setString(1, login);
                statement.setString(2, pass);
                statement.executeQuery();
                result = statement.getResultSet();
                if (result.next()) {
                    return getUserPojo(result);
                }
            }
        } finally {
            DBUtils.closeConnection(connection, statement, result);
        }
        return user;
    }
	
	public User getByName(String name) throws SQLException {
		Connection connection = null;
		ResultSet result = null;
		PreparedStatement statement = null;
		User user = null;

		try {

			connection = ConnectionsPool.getInstance().getConnection();

			// Do we have a connection?
			if (!connection.isClosed()) {
				// We have a MySQL Connection.
				statement = connection
						.prepareStatement("select * from user where name=?");
				statement.setString(1, name);
				statement.executeQuery();
				result = statement.getResultSet();
				if (result.next()) {
					return getUserPojo(result);
				}
			}
		} finally {
		    DBUtils.closeConnection(connection, statement, result);
		}
		return user;
	}

	public User getBySurname(String surname) throws SQLException {
		Connection connection = null;
		ResultSet result = null;
		PreparedStatement statement = null;
		User user = null;

		try {

			connection = ConnectionsPool.getInstance().getConnection();

			// Do we have a connection?
			if (!connection.isClosed()) {
				// We have a MySQL Connection.
				statement = connection
						.prepareStatement("select * from user where surname=?");
				statement.setString(1, surname);
				statement.executeQuery();
				result = statement.getResultSet();
				if (result.next()) {
					return getUserPojo(result);
				}
			}
		} finally {
		    DBUtils.closeConnection(connection, statement, result);
		}
		return user;
	}

	public User getByAddress(String address) throws SQLException {
		Connection connection = null;
		ResultSet result = null;
		PreparedStatement statement = null;
		User user = null;

		try {

			connection = ConnectionsPool.getInstance().getConnection();

			// Do we have a connection?
			if (!connection.isClosed()) {
				// We have a MySQL Connection.
				statement = connection
						.prepareStatement("select * from user where address=?");
				statement.setString(1, address);
				statement.executeQuery();
				result = statement.getResultSet();
				if (result.next()) {
					return getUserPojo(result);
				}
			}
		} finally {
		    DBUtils.closeConnection(connection, statement, result);
		}
		return user;
	}

	public User getByTel(Long tel) throws SQLException {
		Connection connection = null;
		ResultSet result = null;
		PreparedStatement statement = null;
		User user = null;

		try {

			connection = ConnectionsPool.getInstance().getConnection();

			// Do we have a connection?
			if (!connection.isClosed()) {
				// We have a MySQL Connection.
				statement = connection
						.prepareStatement("select * from user where tel=?");
				statement.setLong(1, tel);
				statement.executeQuery();
				result = statement.getResultSet();
				if (result.next()) {
					return getUserPojo(result);
				}
			}
		} finally {
		    DBUtils.closeConnection(connection, statement, result);
		}
		return user;
	}

	public User getByEmail(String email) throws SQLException {
		Connection connection = null;
		ResultSet result = null;
		PreparedStatement statement = null;
		User user = null;

		try {

			connection = ConnectionsPool.getInstance().getConnection();

			// Do we have a connection?
			if (!connection.isClosed()) {
				// We have a MySQL Connection.
				statement = connection
						.prepareStatement("select * from user where email=?");
				statement.setString(1, email);
				statement.executeQuery();
				result = statement.getResultSet();
				if (result.next()) {
					return getUserPojo(result);
				}
			}
		} finally {
		    DBUtils.closeConnection(connection, statement, result);
		}
		return user;
	}

	public User getByBanned(Boolean banned) throws SQLException {
		Connection connection = null;
		ResultSet result = null;
		PreparedStatement statement = null;
		User user = null;

		try {

			connection = ConnectionsPool.getInstance().getConnection();

			// Do we have a connection?
			if (!connection.isClosed()) {
				// We have a MySQL Connection.
				statement = connection
						.prepareStatement("select * from user where banned=?");
				statement.setBoolean(1, banned);
				statement.executeQuery();
				result = statement.getResultSet();
				if (result.next()) {
					return getUserPojo(result);
				}
			}
		} finally {
		    DBUtils.closeConnection(connection, statement, result);
		}
		return user;
	}

	public List<User> getAll() throws SQLException {
		Connection connection = null;
		ResultSet result = null;
		Statement statement = null;

		List<User> userList = new LinkedList<User>();

		try {

			connection = ConnectionsPool.getInstance().getConnection();

			// Do we have a connection?
			if (!connection.isClosed()) {
				// We have a MySQL Connection.
				statement = connection.createStatement();
				statement.executeQuery("select * from user");
				result = statement.getResultSet();
				while (result.next()) {
					userList.add(getUserPojo(result));
				}
				return userList;
			}
		} finally {
		    DBUtils.closeConnection(connection, statement, result);
		}
		return null;
	}

	public Long addUser(User userPojo) throws SQLException {
		Connection connection = null;
		PreparedStatement statement = null;

		try {

			connection = ConnectionsPool.getInstance().getConnection();

			// Do we have a connection?
			if (!connection.isClosed()) {
				// We have a MySQL Connection.
				statement = connection
						.prepareStatement("insert into user (login,pass,name,surname,address,tel,email,role,money) values (?,?,?,?,?,?,?,?,?)",Statement.RETURN_GENERATED_KEYS);
//				statement.setLong(1, userPojo.getId());
				statement.setString(1, userPojo.getLogin());
				statement.setString(2, userPojo.getPass());
				statement.setString(3, userPojo.getName());
				statement.setString(4, userPojo.getSurname());
				statement.setString(5, userPojo.getAddress());
				statement.setString(6, userPojo.getTel());
				statement.setString(7, userPojo.getEmail());
				statement.setString(8, userPojo.getRole());
				statement.setBigDecimal(9, userPojo.getMoney());
				statement.executeUpdate();
				
				ResultSet generatedKeys = statement.getGeneratedKeys();
		        if (generatedKeys.next()) {
		            return generatedKeys.getLong(1);
		        } else {
		            throw new SQLException("Creating user failed, no generated key obtained.");
		        }

			}else{
			    throw new SQLException("Cannot obtain db connection.");
			}
		} finally {
		    DBUtils.closeConnection(connection, statement, null);
		}
	}
	
	
	public void updateMoney(User userPojo) throws SQLException {
        Connection connection = null;
        PreparedStatement statement = null;

        try {

            connection = ConnectionsPool.getInstance().getConnection();

            // Do we have a connection?
            if (!connection.isClosed()) {
                // We have a MySQL Connection.
                statement = connection
                        .prepareStatement("update user set money=? where id=?");
                statement.setBigDecimal(1, userPojo.getMoney());
                statement.setLong(2, userPojo.getId());
                
                statement.executeUpdate();

            }else{
                throw new SQLException("Cannot obtain db connection.");
            }
        } finally {
            DBUtils.closeConnection(connection, statement, null);
        }
    }
	
	public void setBanStatus(User userPojo, Boolean ban) throws SQLException {
        Connection connection = null;
        PreparedStatement statement = null;

        try {

            connection = ConnectionsPool.getInstance().getConnection();

            // Do we have a connection?
            if (!connection.isClosed()) {
                // We have a MySQL Connection.
                statement = connection
                        .prepareStatement("update user set banned=? where id=?");
                statement.setBoolean(1, ban);
                statement.setLong(2, userPojo.getId());
                statement.executeUpdate();
            }
        } finally {
            DBUtils.closeConnection(connection, statement, null);
        }
    }
	
	public void setPassword(User userPojo, String pass) throws SQLException {
        Connection connection = null;
        PreparedStatement statement = null;

        try {

            connection = ConnectionsPool.getInstance().getConnection();

            // Do we have a connection?
            if (!connection.isClosed()) {
                // We have a MySQL Connection.
                statement = connection
                        .prepareStatement("update user set pass=? where id=?");
                statement.setString(1, pass);
                statement.setLong(2, userPojo.getId());
                statement.executeUpdate();
            }
        } finally {
            DBUtils.closeConnection(connection, statement, null);
        }
    }

	public static void main(String[] args) throws SQLException {
		UserDao userDao = new UserDao();
		User user = userDao.getById(1L);
		//System.out.println(user.toString());
		user = userDao.getByLoginPass("Yunnushka", HashGenerator.generateHash("blink003"));
		System.out.println(user);
		// user = userDao.getByLogin("");
	}

	

	/**
	 * @param userRecord
	 *            user record from db
	 * @return User POJO
	 * @throws SQLException
	 */
	private User getUserPojo(ResultSet userRecord) throws SQLException {
		User user = new User();
		user = new User();
		user.setId(userRecord.getLong("id"));
		user.setLogin(userRecord.getString("login"));
		user.setPass(userRecord.getString("pass"));
		user.setName(userRecord.getString("name"));
		user.setSurame(userRecord.getString("surname"));
		user.setAddress(userRecord.getString("address"));
		user.setTel(userRecord.getString("tel"));
		user.setEmail(userRecord.getString("email"));
		user.setBanned(userRecord.getBoolean("banned"));
		user.setRole(userRecord.getString("role"));
		user.setMoney(userRecord.getBigDecimal("money"));
		return user;
	}
}